package dao;

import bean.Commodity;
import bean.HomeData;
import util.DBUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class CommodityDao {

    public static void selectForList(HomeData homeData,List<Integer> commodityIdList) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs=null;
        ArrayList<Commodity> result=new ArrayList<>();
        String query = "SELECT * FROM commodity WHERE id IN (";
        for (int i = 0; i < commodityIdList.size(); i++) {
            query += commodityIdList.get(i);
            if (i != commodityIdList.size() - 1) {
                query += ",";
            }
        }
        query += ")";

        try {
            ps=conn.prepareStatement(query);
            rs=ps.executeQuery();
            while (rs.next()){
                int id=rs.getInt("id");
                String description=rs.getString("description");
                String imgPath=rs.getString("imgpath");
                int sort=rs.getInt("sort");
                BigDecimal price=rs.getBigDecimal("price");
                int quantity=rs.getInt("quantity");
                LocalDateTime createTime=rs.getTimestamp("create_time").toLocalDateTime();
                String creator=rs.getString("creator");

                Commodity commodity=new Commodity();
                commodity.setId(id);
                commodity.setDescription(description);
                commodity.setImgpath(imgPath);
                commodity.setSort(sort);
                commodity.setPrice(price);
                commodity.setQuantitiy(quantity);
                commodity.setCreatetime(createTime);
                commodity.setCreator(creator);

                result.add(commodity);

            }
//            System.out.println(list);
            homeData.setCommodityList(result);
            homeData.setTotalElement(result.size());
        } catch (SQLException e) {
//            users.setId(-1);
            e.printStackTrace();
//            throw new RuntimeException(e);
        }finally {
            DBUtil.closeDBResource(rs,ps,conn);
        }
    }
    public static void selectForKeyword(HomeData homeData){
        Connection conn = DBUtil.getConn();
        String sql="select * from commodity ";
        String totalSql="select count(*) from commodity";
        sql+=" where description like ? ";
        totalSql+=" where description like '%"+homeData.getKeyword()+"%' ";
        if(homeData.getSort()!=0){
//            sql="select  * from (select ROW_NUMBER() over(order by create_time desc)as line,* from commodity) commodity ";
            sql+=" and sort=?";
            totalSql+=" and sort="+homeData.getSort();
        }
        System.out.println(totalSql);
        selectForHomeTotalElement(homeData,totalSql);
        String sqlCurPage=sql+" order by id offset ? rows fetch next ? rows only ";
        PreparedStatement ps=null;
        ResultSet rs=null;
        ArrayList<Commodity> list=new ArrayList<>();

        try {
            ps=conn.prepareStatement(sqlCurPage);
            if(homeData.getSort()==0){
                ps.setString(1,"%"+homeData.getKeyword()+"%");
                if(homeData.getCurPage()==1){
                    ps.setInt(2,(homeData.getCurPage()-1)*homeData.getNumPerPage());
                }else {
                    ps.setInt(2, (homeData.getCurPage() - 1) * homeData.getNumPerPage() + 1);
                }
//                ps.setInt(3,homeData.getCurPage()*homeData.getNumPerPage());
                ps.setInt(3,homeData.getNumPerPage());
            }else{
                ps.setString(1,"%"+homeData.getKeyword()+"%");
                ps.setInt(2,homeData.getSort());
                ps.setInt(3,(homeData.getCurPage()-1)*homeData.getNumPerPage()+1);
                ps.setInt(4,homeData.getNumPerPage());
//                ps.setInt(3,homeData.getCurPage()*homeData.getNumPerPage());
            }
            System.out.println(sqlCurPage);
            rs=ps.executeQuery();
            while (rs.next()){
                int id=rs.getInt("id");
                String description=rs.getString("description");
                String imgPath=rs.getString("imgpath");
                int sort=rs.getInt("sort");
                BigDecimal price=rs.getBigDecimal("price");
                int quantity=rs.getInt("quantity");
                LocalDateTime createTime=rs.getTimestamp("create_time").toLocalDateTime();
                String creator=rs.getString("creator");

                Commodity commodity=new Commodity();
                commodity.setId(id);
                commodity.setDescription(description);
                commodity.setImgpath(imgPath);
                commodity.setSort(sort);
                commodity.setPrice(price);
                commodity.setQuantitiy(quantity);
                commodity.setCreatetime(createTime);
                commodity.setCreator(creator);

                list.add(commodity);

            }
//            System.out.println(list);
            homeData.setCommodityList(list);
//            homeData.setTotalElement(list.size());
        } catch (SQLException e) {
//            users.setId(-1);
            e.printStackTrace();
//            throw new RuntimeException(e);
        }finally {
            DBUtil.closeDBResource(rs,ps,conn);
        }
    }
    public static void selectForHomeBySort(HomeData homeData){
        Connection conn = DBUtil.getConn();
        String sql="select * from commodity ";
        String totalSql="select count(*) from commodity";
        if(homeData.getSort()!=0){
//            sql="select  * from (select ROW_NUMBER() over(order by create_time desc)as line,* from commodity) commodity ";
            sql+=" where sort=?";
            totalSql+=" where sort="+homeData.getSort();
        }
        selectForHomeTotalElement(homeData,totalSql);
        String sqlCurPage=sql+" order by id offset ? rows fetch next ? rows only ";
        PreparedStatement ps=null;
        ResultSet rs=null;
        ArrayList<Commodity> list=new ArrayList<>();

        try {
            ps=conn.prepareStatement(sqlCurPage);
            if(homeData.getSort()==0){
                ps.setInt(1,(homeData.getCurPage()-1)*homeData.getNumPerPage()+1);
//                ps.setInt(3,homeData.getCurPage()*homeData.getNumPerPage());
                ps.setInt(2,homeData.getNumPerPage());
            }else{
                ps.setInt(1,homeData.getSort());
                ps.setInt(2,(homeData.getCurPage()-1)*homeData.getNumPerPage()+1);
                ps.setInt(3,homeData.getNumPerPage());
//                ps.setInt(3,homeData.getCurPage()*homeData.getNumPerPage());
            }
            System.out.println(sqlCurPage);
            rs=ps.executeQuery();
            while (rs.next()){
                int id=rs.getInt("id");
                String description=rs.getString("description");
                String imgPath=rs.getString("imgpath");
                int sort=rs.getInt("sort");
                BigDecimal price=rs.getBigDecimal("price");
                int quantity=rs.getInt("quantity");
                LocalDateTime createTime=rs.getTimestamp("create_time").toLocalDateTime();
                String creator=rs.getString("creator");

                Commodity commodity=new Commodity();
                commodity.setId(id);
                commodity.setDescription(description);
                commodity.setImgpath(imgPath);
                commodity.setSort(sort);
                commodity.setPrice(price);
                commodity.setQuantitiy(quantity);
                commodity.setCreatetime(createTime);
                commodity.setCreator(creator);

                list.add(commodity);

            }
//            System.out.println(list);
            homeData.setCommodityList(list);
        } catch (SQLException e) {
//            users.setId(-1);
            e.printStackTrace();
//            throw new RuntimeException(e);
        }finally {
            DBUtil.closeDBResource(rs,ps,conn);
        }
    }
    public static void selectForHomeTotalElement(HomeData homeData,String sql){
        Connection connection= DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=connection.prepareStatement(sql);
//            if (homeData.getSort()!=0){
//                ps.setInt(1,homeData.getSort());
//            }
            rs=ps.executeQuery();
            int total=0;
            if(rs.next()){
                total=rs.getInt(1);
            }
//            int total=0;
//            while (rs.next()){
//                total++;
//            }
            homeData.setTotalElement(total);
        } catch (SQLException e) {
//            users.setId(-1);
            e.printStackTrace();
//            throw new RuntimeException(e);
        }finally {
            DBUtil.closeDBResource(rs,ps,connection);
        }
    }
}
